﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;

namespace FinalProject
{
    public class UserInformation
    {
        #region Info        

        private List<HoSo> _LstHoSo = new List<HoSo>();

        private List<CongViecUngVien> _LstCongViecUngVien = new List<CongViecUngVien>();

        public List<CongViecUngVien> LstCongViecUngVien
        {
            get { return _LstCongViecUngVien; }
            set { _LstCongViecUngVien = value; }
        }

        public List<HoSo> LstHoSo
        {
            get { return _LstHoSo; }
            set { _LstHoSo = value; }
        }
        private string _strEmail;
        private string _strPassword;
        private string _strLastname;
        private string _strFirstname;
        private DateTime _dtimeBirthday;
        private int _iSex;//0: Nữ, 1: Nam;
        //private string _strAddress;
        private bool _bVietNamese;

        public bool BVietNamese
        {
            get { return _bVietNamese; }
            set { _bVietNamese = value; }
        }
        private string _strNoiCuNguID;

        public string StrNoiCuNguID
        {
            get { return _strNoiCuNguID; }
            set { _strNoiCuNguID = value; }
        }

        public string StrEmail
        {
            get { return _strEmail; }
            set { _strEmail = value; }
        }       

        public string StrPassword
        {
            get { return _strPassword; }
            set { _strPassword = value; }
        }        

        public string StrLastname
        {
            get { return _strLastname; }
            set { _strLastname = value; }
        }
        
        public string StrFirstname
        {
            get { return _strFirstname; }
            set { _strFirstname = value; }
        }
        
        public DateTime DtimeBirthday
        {
            get { return _dtimeBirthday; }
            set { _dtimeBirthday = value; }
        }
        
        public int ISex
        {
            get { return _iSex; }
            set { _iSex = value; }
        }
        
        //public string StrAddress
        //{
        //    get { return _strAddress; }
        //    set { _strAddress = value; }
        //}

        //Khóa ngoại liên kết UMS_tblUserAccount và HR_tblEmpCV
        private string _strEmpID;

        public string StrEmpID
        {
            get { return _strEmpID; }
            set { _strEmpID = value; }
        }
        #endregion        

        public UserInformation()
        {
        }

        //public UserInformation(string strEmail, string strPassword, string strLastname, string strFirstname, DateTime dtimeBirthday, int iSex, string strAddress)
        //{
        //    _strEmail = strEmail;
        //    _strPassword = strPassword;
        //    _strLastname = strLastname;
        //    _strFirstname = strFirstname;
        //    _dtimeBirthday = dtimeBirthday;
        //    _iSex = iSex;
        //    _strAddress = strAddress;
        //}

        public UserInformation(string strEmail, string strPassword, string strLastname, string strFirstname, DateTime dtimeBirthday, int iSex, bool bVietNamese, string strNoiCuNguID)
        {
            _strEmail = strEmail;
            _strPassword = strPassword;
            _strLastname = strLastname;
            _strFirstname = strFirstname;
            _dtimeBirthday = dtimeBirthday;
            _iSex = iSex;
            _bVietNamese = bVietNamese;
            _strNoiCuNguID = strNoiCuNguID;
        }

        //Dữ liệu cho user bên table UMS_tblUserAccount
        public void updateUserInformation1(string strEmail, string strPassword)
        {
            _strEmail = strEmail;
            _strPassword = strPassword;
        }

        //Dữ liệu cho user bên table HR_tblEmpCV
        //public void updateUserInformation2(string strLastName, string strFirstName, DateTime dtimeBirthday, int iSex, string strAddress)
        //{
        //    _strLastname = strLastName;
        //    _strFirstname = strFirstName;
        //    _dtimeBirthday = dtimeBirthday;
        //    _iSex = iSex;
        //    _strAddress = strAddress;
        //}
        public void updateUserInformation2(string strLastName, string strFirstName, DateTime dtimeBirthday, int iSex, bool bVietNamese, string strNoiCuNguID)
        {
            _strLastname = strLastName;
            _strFirstname = strFirstName;
            _dtimeBirthday = dtimeBirthday;
            _iSex = iSex;
            _bVietNamese = bVietNamese;
            _strNoiCuNguID = strNoiCuNguID;
        }

        //Thêm vào cơ sở dữ liệu
        public int insertUser()
        {            
            //CConnection.openConnection();

            //Thông tin bắt buộc nhập:
            string UserGroupName = "Ứng viên nhập từ WebOnline Tool";
            DateTime EffectiveDate = DateTime.Now;            
            bool FValid = true;
            bool FAdm = false;
            int EmpID = 0;
            
            //SqlCommand sqlCommand = CConnection.getSqlCommand();
            SqlCommand sqlCommand = Utility.sqlCommand;

            //Lấy giá trị cột value trong bảng Index, đếm số user
            sqlCommand.CommandText = "SELECT Value FROM tblIndex";
            SqlDataReader dataReader = sqlCommand.ExecuteReader();          
            while (dataReader.Read())
            {
                EmpID = (int)dataReader[0] + 1;    
            }            
            
            dataReader.Close();

            int i = 0;
            try
            {
                StrEmpID = EmpID.ToString();
                string strEffectiveDate = EffectiveDate.ToString("M/d/yyyy");
                sqlCommand.CommandText = String.Format("insert into UMS_tblUserAccount (UserGroupID, Password, UserGroupName, EffectiveDate, FValid, FAdm, EmpID) values (N'{0}', N'{1}', N'{2}', '{3}', {4}, {5}, '{6}')",
                    _strEmail, _strPassword, UserGroupName, strEffectiveDate, FValid == true ? 1 : 0, FAdm == true ? 1 : 0, EmpID.ToString());
                i++;
                int resutl1 = sqlCommand.ExecuteNonQuery();

                sqlCommand.CommandText = String.Format("insert into HR_tblEmp (EmpID, Active) values('{0}', {1})", EmpID.ToString(), 1);
                i++;
                int result2 = sqlCommand.ExecuteNonQuery();

                //Nếu là người VN thì chèn Born_LSProvinceID: ID Tỉnh thành và LSNationalityID: ""
                //Nếu là người nước ngoài thì chèn Born_LSProvinceID: "" và LSNationalityID: ID Quốc Gia
                string _strDOB = _dtimeBirthday.ToString("M/d/yyyy");
                if (_bVietNamese)
                {
                    sqlCommand.CommandText = String.Format("insert into HR_tblEmpCV (EmpID, VLastName, VFirstName, DOB, Gender, Born_LSProvinceID) values ('{0}', N'{1}', N'{2}', '{3}', '{4}', '{5}')",
                    EmpID.ToString(), _strLastname, _strFirstname, _strDOB, _iSex.ToString(), _strNoiCuNguID);
                }
                else
                {
                    sqlCommand.CommandText = String.Format("insert into HR_tblEmpCV (EmpID, VLastName, VFirstName, DOB, Gender, LSNationalityID) values ('{0}', N'{1}', N'{2}', '{3}', '{4}', '{5}')",
                    EmpID.ToString(), _strLastname, _strFirstname, _strDOB, _iSex.ToString(), _strNoiCuNguID);
                }
                i++;
                int result3 = sqlCommand.ExecuteNonQuery();

                sqlCommand.CommandText = String.Format("update tblIndex set Value = {0}", EmpID);
                i++;
                int result4 = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception eX)
            {
                return i;
            }
            //CConnection.closeConnection();
            return 0;
        }

        public void UpdateUserAccount(string newEmail, string newPassword)
        {
            try
            {
                SqlCommand command = Utility.sqlCommand;
                command.CommandText = String.Format("UPDATE UMS_tblUserAccount SET UserGroupID='{0}', Password='{1}' WHERE UserGroupID='{2}'", newEmail, newPassword, StrEmail);                                
                command.ExecuteNonQuery();

                StrEmail = newEmail;
                StrPassword = newPassword;
            }
            catch { }
        }

        public void UpdateUserInfo(string newLastName, string newFirstName, DateTime newBirthday, int newSex, string newNoiCuNguID, bool isVietNamese)
        {
            try
            {
                SqlCommand command = Utility.sqlCommand;
                if (isVietNamese)
                {
                    command.CommandText = String.Format("UPDATE HR_tblEmpCV SET VLastName=N'{0}', VFirstName=N'{1}', DOB='{2}', Gender='{3}', Born_LSProvinceID='{4}', LSNationalityID=NULL WHERE EmpID='{5}'",
                        newLastName, newFirstName, newBirthday.ToString("M/d/yyyy"), newSex.ToString(), newNoiCuNguID, StrEmpID);
                }
                else
                {
                    command.CommandText = String.Format("UPDATE HR_tblEmpCV SET VLastName='{0}', VFirstName='{1}', DOB='{2}', Gender='{3}', Born_LSProvinceID=NULL, LSNationalityID='{4}' WHERE EmpID='{5}'",
                        newLastName, newFirstName, newBirthday.ToString("M/d/yyyy"), newSex.ToString(), newNoiCuNguID, StrEmpID);
                }
                command.ExecuteNonQuery();

                StrFirstname = newFirstName;
                StrLastname = newLastName;
                DtimeBirthday = newBirthday;
                ISex = newSex;
                StrNoiCuNguID = newNoiCuNguID;
                BVietNamese = isVietNamese;
            }
            catch { }
        }

        public bool checkUserAccount(string strUser, string strPassword)
        {
            bool isRight = false; 
            
            SqlCommand thisCommand = Utility.sqlCommand;
            thisCommand.CommandText = "SELECT UserGroupID, Password FROM UMS_tblUserAccount";
                        
            #region Đếm số lượng user, trường hợp đăng nhập bằng tài khoản cuối cùng.
            SqlDataReader thisReader = thisCommand.ExecuteReader();
            while (thisReader.Read())
            {                
                //Nếu đúng user thì break
                if( strUser.CompareTo(thisReader["UserGroupID"].ToString()) == 0)
                {
                    if(strPassword.CompareTo(thisReader["Password"].ToString()) == 0)
                    {
                        isRight = true;
                    }
                }
            }
            thisReader.Close();
            #endregion

            if (isRight)
            {
                #region
                thisCommand.CommandText = String.Format("SELECT UserGroupID, Password, UserGroupName, EffectiveDate, FValid, FAdm, EmpID FROM UMS_tblUserAccount"
                   + " WHERE UserGroupID = '{0}' and Password = '{1}'", strUser, strPassword);
                SqlDataReader reader2 = thisCommand.ExecuteReader();
                while (reader2.Read())
                {                    
                    updateUserInformation1(reader2[0].ToString(), reader2[1].ToString());
                    _strEmpID = reader2[6].ToString();
                }
                reader2.Close();
                #endregion

                #region Lay thong tin User
                thisCommand.CommandText = String.Format("SELECT EmpID, VLastName, VFirstName, DOB, Gender, Born_LSProvinceID, LSNationalityID FROM HR_tblEmpCV"
                    + " WHERE EmpID = '{0}'", _strEmpID);
                reader2 = thisCommand.ExecuteReader();
                while (reader2.Read())
                {
                    updateUserInformation2(reader2[1].ToString(), reader2[2].ToString(), Convert.ToDateTime(reader2[3].ToString()), Convert.ToInt16(reader2[4].ToString()), 
                        reader2[5].ToString().CompareTo("")!=0?true:false, reader2[5].ToString().CompareTo("")!=0?reader2[5].ToString():reader2[6].ToString());
                }
                reader2.Close();

                GetLstCongViecUngVien(thisCommand);
                Utility.UserCurrent = this;
                #endregion                

                #region Lay danh sach Ho so
                //Lấy list hồ sơ gán vào cho user
                //Có empID rùi.
                //Đầu tiên lấy từ bản hồ sơ ứng viên
                //Một user có nhiều hồ sơ, một hồ sơ có 1 thông tin liên hệ, lấy danh sách hồ sơ dựa vào empID,
                //Một hồ sơ thì có nhiều kinh nghiệm làm việc, học vấn bằng cấp, thông tin tham khảo
                

                #region Variable
                List<int> lstIDHoSoCurrent = new List<int>();
                HoSo tempHoSo = null;
                ThongTinLienHe thongTinLienHe = null;
                string hoVaLot = "";
                string ten = "";
                DateTime ngaySinh = DateTime.Now;
                bool gioiTinh = true; 
                string pathImage ="";
                RootObject tinhTrangHonNhan = null;
                string diaChi = "";
                RootObject quocGia = null;
                RootObject tinhThanh = null;
                RootObject quanHuyen = null;
                string sdt = "";
                string email = strUser;
                DateTime ngayCapNhat = DateTime.Now;

                int namKinhNghiem = 0;
                List<NgoaiNgu> lstNgoaiNgu = new List<NgoaiNgu>();
                List<TrinhDoNgoaiNgu> lstTrinhDoNgoaiNgu = new List<TrinhDoNgoaiNgu>();
                string viTriMongMuon = "";
                ChucVu chucVu = new ChucVu();
                string kyNangNoiBat = "";
                List<TinhThanh> lstNoiLamViec = new List<TinhThanh>();
                List<NganhNghe> lstNganhNghe = new List<NganhNghe>();
                float mucLuong = 0;
                bool isChoPhepTimKiem = true;

                string mucTieuNgheNghiep = "";
                string thanhTichNoiBat = "";

                List<KinhNghiemLamViec> lstKinhNghiemLamViec = new List<KinhNghiemLamViec>();
                List<HocVanBangCap> lstHocVanBangCap = new List<HocVanBangCap>();
                List<ThongTinThamKhao> lstThongTinThamKhao = new List<ThongTinThamKhao>();
                #endregion

                #region Lay thong tin chung cua Ho so
                thisCommand.CommandText = String.Format(@"select ID, LastName, FirstName, NgaySinh, GioiTinh, TinhTrangHonNhanID, DiaChi, 
                    QuocGiaID, TinhThanhID, QuanHuyenID, SDT, Image, EmpID, SoNamKinhNghiem,
                    ViTriMongMuon, ChucVuID, NgoaiNgu1ID, NgoaiNgu2ID, NgoaiNgu3ID, TrinhDoNN1ID,
                    TrinhDoNN2ID, TrinhDoNN3ID, NoiLamViec1ID, NoiLamViec2ID, NoiLamViec3ID,
                    NganhNghe1ID, NganhNghe2ID, NganhNghe3ID, MucLuong, IsChoPhepTimKiem,
                    MucTieuNgheNghiep, ThanhTichNoiBat, KyNangNoiBat, NgayCapNhatGanNhat
                    from tblHoSoUngVien where EmpID = '{0}'", _strEmpID);

                reader2 = thisCommand.ExecuteReader();

                while (reader2.Read())
                {
                    #region Thong tin chung
                    int idhosocurrent = int.Parse(reader2["ID"].ToString());
                    lstIDHoSoCurrent.Add(idhosocurrent);
                    hoVaLot = reader2["LastName"].ToString();
                    ten = reader2["FirstName"].ToString();
                    ngaySinh = DateTime.Parse(reader2["NgaySinh"].ToString());
                    gioiTinh = bool.Parse(reader2["GioiTinh"].ToString());
                    pathImage = reader2["Image"].ToString();
                    tinhTrangHonNhan = Utility.getTinhTrangHonNhan(reader2["TinhTrangHonNhanID"].ToString());
                    diaChi = reader2["DiaChi"].ToString();
                    quocGia = Utility.getQuocGiaByID(reader2["QuocGiaID"].ToString());
                    tinhThanh = Utility.getTinhThanhByID(reader2["TinhThanhID"].ToString());
                    quanHuyen = Utility.getQuanHuyenByID(reader2["QuanHuyenID"].ToString());
                    sdt = reader2["SDT"].ToString();
                    ngayCapNhat = DateTime.Parse(reader2["NgayCapNhatGanNhat"].ToString());          
                    #endregion                    

                    #region Thong tin day du
                    thongTinLienHe = new ThongTinLienHe(hoVaLot, ten, ngaySinh, gioiTinh, pathImage, tinhTrangHonNhan,
                        diaChi, quocGia, tinhThanh, quanHuyen, sdt, email);

                    namKinhNghiem = int.Parse(reader2["SoNamKinhNghiem"].ToString());
                    viTriMongMuon = reader2["ViTriMongMuon"].ToString();
                    chucVu = Utility.getChucVuByID(reader2["ChucVuID"].ToString());
                    mucLuong = int.Parse(reader2["MucLuong"].ToString());
                    isChoPhepTimKiem = bool.Parse(reader2["IsChoPhepTimKiem"].ToString());
                    mucTieuNgheNghiep = reader2["MucTieuNgheNghiep"].ToString();
                    thanhTichNoiBat = reader2["ThanhTichNoiBat"].ToString();
                    kyNangNoiBat = reader2["KyNangNoiBat"].ToString();
                    #endregion

                    #region lstNgoaiNgu lstTrinhDoNgoaiNgu
                    if (reader2["NgoaiNgu1ID"] != null)
                    {
                        lstNgoaiNgu.Add(Utility.getNgoaiNguByID(reader2["NgoaiNgu1ID"].ToString()));
                        if (reader2["NgoaiNgu2ID"] != null)
                        {
                            lstNgoaiNgu.Add(Utility.getNgoaiNguByID(reader2["NgoaiNgu2ID"].ToString()));
                            if (reader2["NgoaiNgu3ID"] != null)
                            {
                                lstNgoaiNgu.Add(Utility.getNgoaiNguByID(reader2["NgoaiNgu3ID"].ToString()));
                            }

                        }
                    }
                    #endregion

                    #region lstNoiLamViec
                    if (reader2["NoiLamViec1ID"] != null)
                    {
                        lstNoiLamViec.Add(Utility.getTinhThanhTTByID(reader2["NoiLamViec1ID"].ToString()));
                        if (reader2["NoiLamViec2ID"] != null)
                        {
                            lstNoiLamViec.Add(Utility.getTinhThanhTTByID(reader2["NoiLamViec2ID"].ToString()));
                            if (reader2["NoiLamViec3ID"] != null)
                            {
                                lstNoiLamViec.Add(Utility.getTinhThanhTTByID(reader2["NoiLamViec3ID"].ToString()));
                            }
                        }
                    }
                    #endregion

                    #region lstNganhNghe
                    if (reader2["NganhNghe1ID"] != null)
                    {
                        lstNganhNghe.Add(Utility.getNganhNgheNNByID(reader2["NganhNghe1ID"].ToString()));
                        if (reader2["NganhNghe2ID"] != null)
                        {
                            lstNganhNghe.Add(Utility.getNganhNgheNNByID(reader2["NganhNghe2ID"].ToString()));
                            if (reader2["NganhNghe3ID"] != null)
                            {
                                lstNganhNghe.Add(Utility.getNganhNgheNNByID(reader2["NganhNghe3ID"].ToString()));
                            }
                        }
                    }
                    #endregion

                    tempHoSo = new HoSo(namKinhNghiem, lstNgoaiNgu, lstTrinhDoNgoaiNgu, viTriMongMuon,
                        chucVu, lstNoiLamViec, lstNganhNghe, mucLuong, isChoPhepTimKiem, mucTieuNgheNghiep,
                        thanhTichNoiBat, null, null, kyNangNoiBat,
                        null, thongTinLienHe);
                    tempHoSo.ID = idhosocurrent.ToString();
                    tempHoSo.NgayCapNhat = ngayCapNhat;

                    _LstHoSo.Add(tempHoSo);

                    namKinhNghiem = 0;
                    lstNgoaiNgu = new List<NgoaiNgu>();
                    lstTrinhDoNgoaiNgu = new List<TrinhDoNgoaiNgu>();
                    viTriMongMuon = "";
                    chucVu = new ChucVu();
                    lstNoiLamViec = new List<TinhThanh>();
                    lstNganhNghe = new List<NganhNghe>();
                    mucLuong = 0;
                    isChoPhepTimKiem = true;
                    mucTieuNgheNghiep = "";
                    thanhTichNoiBat = "";
                    kyNangNoiBat = "";
                    thongTinLienHe = new ThongTinLienHe();
                }
                reader2.Close();
                #endregion

                for (int i = 0; i < lstIDHoSoCurrent.Count; i++)
                {
                    lstKinhNghiemLamViec = new List<KinhNghiemLamViec>();
                    thisCommand.CommandText = @"select ID, HoSoID, ChucDanh, CongTy, ChucVuID, NganhNgheID, NgayBatDau, NgayKetThuc, NhiemVuThanhTich
from tblKinhNghiemLamViec where HoSoID = '" + lstIDHoSoCurrent[i] +"'";
                    reader2 = thisCommand.ExecuteReader();
                    while (reader2.Read())
                    {
                        string congty = reader2["CongTy"].ToString();
                        string chucdanh = reader2["ChucDanh"].ToString();
                        RootObject cvtemp = new RootObject(Utility.getChucVuByID(reader2["ChucVuID"].ToString()).ID,
                            Utility.getChucVuByID(reader2["ChucVuID"].ToString()).ID) ;
                        RootObject nntemp = Utility.getNganhNgheByID(reader2["NganhNgheID"].ToString());
                        string nhiemvuthanhtich = reader2["NhiemVuThanhTich"].ToString();
                        DateTime nbd = DateTime.Parse(reader2["NgayBatDau"].ToString());
                        DateTime nkt = DateTime.Parse(reader2["NgayKetThuc"].ToString());
                        KinhNghiemLamViec knlv = new KinhNghiemLamViec(congty, chucdanh, cvtemp, nntemp, nbd, nkt, nhiemvuthanhtich);
                        lstKinhNghiemLamViec.Add(knlv);
                        congty = "";
                        chucdanh = "";
                        cvtemp = new RootObject();
                        nntemp = new RootObject();
                        nbd = new DateTime();
                        nkt = new DateTime();
                        nhiemvuthanhtich = "";

                    }
                    _LstHoSo[i].LstKinhNghiemLamViec = lstKinhNghiemLamViec;
                    reader2.Close();
                }

                for (int i = 0; i < lstIDHoSoCurrent.Count; i++)
                {
                    lstHocVanBangCap = new List<HocVanBangCap>();
                    thisCommand.CommandText = @"select ID, HoSoID, BangCapID, TruongID, ChuyenNganhID, NgayBatDau, NgayKetThuc, ThongTinLienQuan 
from tblHocVanBangCap where HoSoID='" + lstIDHoSoCurrent[i] + "'";
                    reader2 = thisCommand.ExecuteReader();
                    while (reader2.Read())
                    {
                        RootObject bangcap = Utility.getBangCapByID(reader2["BangCapID"].ToString());
                        RootObject truong = Utility.getTruongByID(reader2["TruongID"].ToString());
                        RootObject chuyennganh = Utility.getChuyenNganhByID(reader2["ChuyenNganhID"].ToString());
                        DateTime nbd = DateTime.Parse(reader2["NgayBatDau"].ToString());
                        DateTime nkt = DateTime.Parse(reader2["NgayKetThuc"].ToString());
                        string thongtin = reader2["ThongTinLienQuan"].ToString();
                        HocVanBangCap hvbc = new HocVanBangCap(bangcap, truong, chuyennganh, nbd, nkt, thongtin);
                        lstHocVanBangCap.Add(hvbc);
                        
                       
                        bangcap = new RootObject();
                        truong = new RootObject();
                        chuyennganh = new RootObject();
                        nbd = new DateTime();
                        nkt = new DateTime();
                        thongtin = "";
                    }
                    _LstHoSo[i].LstHocVanBangCap = lstHocVanBangCap;
                    reader2.Close();
                }


                for (int i = 0; i < lstIDHoSoCurrent.Count; i++)
                {
                    lstThongTinThamKhao = new List<ThongTinThamKhao>();
                    thisCommand.CommandText = @"select ID, HoSoID, HoTen, ChucDanh, CongTy, Email, SDT
from tblThongTinThamKhao where HoSoID='" + lstIDHoSoCurrent[i] + "'";
                    reader2 = thisCommand.ExecuteReader();
                    while (reader2.Read())
                    {
                        string hoten = reader2["HoTen"].ToString();
                        string chucdanh = reader2["ChucDanh"].ToString();
                        string congty = reader2["CongTy"].ToString();
                        string emailhi = reader2["Email"].ToString();
                        string sdthi = reader2["SDT"].ToString();
                        ThongTinThamKhao tttk = new ThongTinThamKhao(hoten, chucdanh, congty, email, sdt);
                       
                        lstThongTinThamKhao.Add(tttk);
                        
                        hoten = "";
                        chucdanh = "";
                        congty = "";
                        emailhi = "";
                        sdthi = "";
                    }
                    _LstHoSo[i].LstThongTinThamKhao = lstThongTinThamKhao;
                    reader2.Close();
                }
                return true;
                #endregion
            }
            
            
            return false;
        }


        public List<HoSo> getlisthoso()
        {
            return _LstHoSo;
        }


        public void GetLstCongViecUngVien(SqlCommand command)
        {
            try
            {
                command.CommandText = String.Format("SELECT ID, CongViecID, EmpID, IsNopHoSo, Ngay, NoiLamViecID FROM tblCongViecUngVien WHERE EmpID='{0}'", _strEmpID);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    CongViecUngVien congViec = new CongViecUngVien(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), bool.Parse(reader[3].ToString()), DateTime.Parse(reader[4].ToString()), reader[5].ToString());
                    LstCongViecUngVien.Add(congViec);
                }
                reader.Close();
            }
            catch { }
        }

        public void DeleteCongViecUngVien(string id)
        {
            try
            {
                SqlCommand command = Utility.sqlCommand;
                command.CommandText = String.Format("DELETE FROM tblCongViecUngVien WHERE ID = '{0}'", id);
                command.ExecuteNonQuery();
            }
            catch { }
        }

        public void InsertCongViecUngVien(CongViecUngVien congViec)
        {
            try
            {
                if (congViec != null)
                {
                    SqlCommand command = Utility.sqlCommand;

                    // Get Index of CongViecUngVien;
                    command.CommandText = "SELECT ValueCongViecUngVien FROM tblIndex";
                    SqlDataReader dataReader = command.ExecuteReader();
                    while (dataReader.Read())
                    {
                        int strId = (int)dataReader[0] + 1;
                        congViec.ID = strId.ToString();
                    }
                    dataReader.Close();

                    // Insert CongViecUngVien                    
                    command.CommandText = String.Format("INSERT INTO tblCongViecUngVien (ID, CongViecID, EmpID, IsNopHoSo, Ngay, NoiLamViecID) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')",
                        congViec.ID, congViec.CongViecID, congViec.EmpID, congViec.IsNopHoSo, congViec.Ngay.ToString("M/d/yyyy"), congViec.NoiLamViecID);
                    command.ExecuteNonQuery();

                    // Update Index of CongViecUngVien
                    command.CommandText = String.Format("UPDATE tblIndex SET ValueCongViecUngVien = {0}", int.Parse(congViec.ID));
                    command.ExecuteNonQuery();

                    LstCongViecUngVien.Add(congViec);
                }
            }
            catch { Exception e; }
        }

        public void UpdateCongViecUngVien(CongViecUngVien congViec)
        {
            try
            {
                SqlCommand command = Utility.sqlCommand;
                command.CommandText = String.Format("UPDATE tblCongViecUngVien SET IsNopHoSo = '{0}' WHERE ID = '{1}'", congViec.IsNopHoSo, congViec.ID);
                command.ExecuteNonQuery();

                for (int i = 0; i < LstCongViecUngVien.Count; i++)
                {
                    if (LstCongViecUngVien[i].ID == congViec.ID)
                    {
                        LstCongViecUngVien[i] = congViec;
                        break;
                    }
                }
            }
            catch { }
        }

        public CongViecUngVien GetCongViecUngVienByID(string id)
        {
            try
            {
                for (int i = 0; i < LstCongViecUngVien.Count; i++)
                {
                    if (LstCongViecUngVien[i].ID == id)
                        return LstCongViecUngVien[i];
                }
                return null;
            }
            catch{ return null;}
        }        

    }
}